package cn.slt.service.impl;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.slt.service.MD_libraryDSIDService;
import cn.slt.service.MD_testConnection;

import com.github.abel533.InsertBatch;
import com.github.abel533.database.Dialect;
import com.github.abel533.database.IntrospectedColumn;
import com.github.abel533.database.IntrospectedTable;
import com.github.abel533.database.SimpleDataSource;
import com.github.abel533.utils.DBMetadataUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Service
@Transactional
public class MD_testConnectionImpl implements MD_testConnection {
	SimpleDataSource dataSource=null;
	String pre=null;
	@Override
	public boolean testConnection(String url, String username, String password, String owner, String database) {
		boolean mark = false;
		if(database.equals("oracle")){
			pre="jdbc:oracle:thin:@";
		      dataSource = new SimpleDataSource(
				/*
				 * Dialect.MYSQL, "jdbc:mysql://localhost:3306/test", "root",
				 * "123"
				 */
				Dialect.ORACLE,
				// "jdbc:oracle:thin:@121.248.200.5:1521:orcl",
				// "swjsysq",
				// "swjsysq"
				"jdbc:oracle:thin:@" + url, username, password
		// Dialect.ORACLE,
		// "jdbc:oracle:thin:@10.32.1.6:1521/jssworcl",
		// "r_jssq",
		// "r_jssq" //jssqsys
		);}else {
			pre="jdbc:jtds:sqlserver://";
			dataSource = new SimpleDataSource(
					/*
					 * Dialect.MYSQL, "jdbc:mysql://localhost:3306/test",
					 * "root", "123"
					 */
					Dialect.SQLSERVER,
    		          "jdbc:jtds:sqlserver://"+url,
    		           username,
    		          password   
			// Dialect.ORACLE,
			// "jdbc:oracle:thin:@10.32.1.6:1521/jssworcl",
			// "r_jssq",
			// "r_jssq"
			);
		}

		SimpleDataSource dataSource2 = new SimpleDataSource(
				/*
				 * Dialect.MYSQL, "jdbc:mysql://localhost:3306/test", "root",
				 * "123"
				 */
//				 Dialect.ORACLE,
//				 "jdbc:oracle:thin:@121.248.200.5:1521:orcl",
//				 "SLMETADATA",
//				 "SLMETADATA"
			Dialect.ORACLE, "jdbc:oracle:thin:@10.32.25.20:1521/arcgis", "SLMETADATA", "SLMETADATA"
				 );
		// String str="swjsysq";
		String str = owner;

		DBMetadataUtils dbMetadataUtils = null;
		DBMetadataUtils dbMetadataUtils2 = null;

		try {

			dbMetadataUtils = new DBMetadataUtils(dataSource);

			System.out.println("数据库1连接成功");
			dbMetadataUtils2 = new DBMetadataUtils(dataSource2);
			Connection conn = dbMetadataUtils2.getConnection();

			System.out.println("数据库2连接成功");

			PreparedStatement sm = null;
			String mdcode = null;
			String test = null;
			// String mdcode=dbMetadataUtils.getIntrospector().getmdCode(conn);
			int dscode1 = dbMetadataUtils.getIntrospector().getdsCode1(conn);
			int dscode2 = dbMetadataUtils.getIntrospector().getdsCode2(conn);
			int dscode3 = dbMetadataUtils.getIntrospector().getdsCode3(conn);

			sm = conn.prepareStatement(
					"select mdcode from MD_Metadata where owner=:1 and location=:2 order by builddate desc");
			sm.setString(1, owner.toUpperCase());
			sm.setString(2, pre + url);
			ResultSet rs = sm.executeQuery();
			while (rs.next()) {
				mdcode = rs.getString(1);
				break;
			}
			rs.close();
			if (mdcode == null) {
				System.out.println("无元数据信息");
				return false;
			}

			sm = conn.prepareStatement("select mdcode from MD_LibraryDSID where location=:1 and dsname=:2");
			sm.setString(1,pre+ url);
			sm.setString(2, owner.toUpperCase());
			rs = sm.executeQuery();
			while (rs.next()) {
				test = rs.getString(1);
				break;
			}
			rs.close();
			if (test != null) {
				System.out.println("重复数据库");
				return false;
			}
			InsertBatch insert = new InsertBatch();
			ArrayList<Object[]> resultlist1 = new ArrayList<Object[]>();
			ArrayList<Object[]> resultlist2 = new ArrayList<Object[]>();
			ArrayList<Object[]> resultlist3 = new ArrayList<Object[]>();
			String sql1 = "INSERT INTO MD_LIBRARYDSID(mdCode,dsCode,builddate,charset,version,dsname,location)  VALUES(?,?,?,?,?,?,?)";
			String sql2 = "INSERT INTO MD_CLASSDSID(mdCode,supDscode,dsCode,dsname,beizhu,builddate)  VALUES(?,?,?,?,?,?)";
			String sql3 = "INSERT INTO MD_OBJDSID(mdCode,supDsCode,dsCode,dsName,beizhu,builddate,ispk,dataType)  VALUES(?,?,?,?,?,?,?,?)";

			Object[] obj1 = new Object[7];
			Object[] obj2 = new Object[6];
			Object[] obj3 = new Object[8];
			// obj1[0]=null;
			obj1[0] = mdcode;
			obj1[1] = dscode1 + 1;
			try{
			obj1[2] = dbMetadataUtils.getIntrospector().getDatabaseCreatedTime(dbMetadataUtils.getDefaultConfig(), str);}
			catch(Exception e){
				
			}
			try {
				obj1[3] = dbMetadataUtils.getIntrospector().getCharset(dbMetadataUtils.getDefaultConfig());
			} catch (Exception e) {
				// TODO: handle exception
			}
			
			obj1[4] = dbMetadataUtils.getDatabaseMetaData().getDatabaseProductVersion();
			// obj1[5]=dbMetadataUtils.getDefaultConfig().getSchemaPattern();
			obj1[5] = str.toUpperCase();
			obj1[6] = dbMetadataUtils.getDatabaseMetaData().getURL();
			resultlist1.add(obj1);

			for (Object[] objects : resultlist1) {

				System.out.println();
				for (int i = 0; i < 7; i++) {
					System.out.print(objects[i] + "\t");

				}
				System.out.println(" ");
			}
			// insert.saveResults(resultlist1, sql1,conn, 7);

			/*
			 * Statement statement
			 * =dbMetadataUtils.getConnection().createStatement(); String sql=
			 * "SELECT CREATED FROM USER_OBJECTS WHERE OBJECT_NAME='FX_APP_VERSION'"
			 * ; String sql1="SELECT * FROM USER_TABLES";
			 * 
			 * ResultSet rs=statement.executeQuery(sql1); int col =
			 * rs.getMetaData().getColumnCount(); while(rs.next()){ for (int i =
			 * 1; i <= col; i++) { System.out.print(rs.getString(i) + "\t"); //
			 * if ((i == 2) && (rs.getString(i).length() < 8)) { //
			 * System.out.print("\t"); // } } System.out.println("-------"); }
			 * rs.close();
			 */
			// System.out.println("数据库catlogs："+ dbMetadataUtils.getCatalogs());
			// System.out.println("数据库schema："+dbMetadataUtils.getSchemas());
			// System.out.println("数据库schematerm："+dbMetadataUtils.getDatabaseMetaData().getSchemaTerm());
			// System.out.println("数据库username："+dbMetadataUtils.getDatabaseMetaData().getUserName());
			// System.out.println("数据库URL: " +
			// dbMetadataUtils.getDatabaseMetaData().getURL());
			// System.out.println("是否允许只读:" +
			// dbMetadataUtils.getDatabaseMetaData().isReadOnly());
			// System.out.println("数据库的产品名称:" +
			// dbMetadataUtils.getDatabaseMetaData().getDatabaseProductName());
			// System.out.println("数据库的版本:" +
			// dbMetadataUtils.getDatabaseMetaData().getDatabaseProductVersion());
			// System.out.println("数据库的schemapattern:" +
			// dbMetadataUtils.getDefaultConfig().getSchemaPattern());
			// System.out.println("数据库建立时间:" +
			// dbMetadataUtils.getIntrospector().getDatabaseCreatedTime(dbMetadataUtils.getDefaultConfig()));
			// System.out.println("数据库的字符集:" +
			// dbMetadataUtils.getIntrospector().getCharset(dbMetadataUtils.getDefaultConfig()));

			/*
			 * System.out.println("数据库中使用的表类型"); ResultSet rs =
			 * dbMetadataUtils.getDatabaseMetaData().getClientInfoProperties();
			 * while (rs.next()) { System.out.println(rs.getString(1)); }
			 * rs.close();
			 */

			List<IntrospectedTable> list = dbMetadataUtils.introspectTables(dbMetadataUtils.getDefaultConfig(), str);
			//
			// for (IntrospectedTable base : list) {
			// System.out.println(base.getRemarks() + ":"); }
			// System.out.println("---------------------------");
			// for (IntrospectedTable base : list) {
			// System.out.println(base.getCatalog() + ":"); }
			// System.out.println("---------------------------");
			// for (IntrospectedTable base : list) {
			// System.out.println(base.getName() + ":"); }
			// System.out.println("---------------------------");
			//

			for (IntrospectedTable table : list) {
				mark = true;
				if (mark)
					break;

				obj2[0] = mdcode;
				obj2[1] = dscode1 + 1;
				obj2[2] = ++dscode2;
				obj2[3] = table.getName();
				obj2[4] = table.getRemarks();
				try{
				obj2[5] = dbMetadataUtils.getIntrospector().getTableCreatedTime(dbMetadataUtils.getDefaultConfig(), str,
						table.getName());}
				catch (Exception e){
					
				}

				resultlist2.add(obj2);
				for (Object[] objects2 : resultlist2) {

					System.out.println();

					for (int i = 0; i < 6; i++) {
						System.out.print(objects2[i] + "\t");

					}
					System.out.println(" ");
				}
				// insert.saveResults(resultlist2, sql2,conn,6);

				resultlist2.clear();
				// System.out.println(table.getName() + ":");
				// System.out.println(table.getRemarks() + ":");
				// System.out.println("表建立时间:" +
				// dbMetadataUtils.getIntrospector().getTableCreatedTime(dbMetadataUtils.getDefaultConfig(),table.getName()));
				// System.out.println("表DML时间:" +
				// dbMetadataUtils.getIntrospector().getTableDMLTime(dbMetadataUtils.getDefaultConfig(),table.getName()));
				// System.out.println("表Rows:" +
				// dbMetadataUtils.getIntrospector().getRowNum(dbMetadataUtils.getDefaultConfig(),table.getName()));
				// System.out.println("表AnalyzedTime:" +
				// dbMetadataUtils.getIntrospector().getAnalyzedTime(dbMetadataUtils.getDefaultConfig(),table.getName()));
				for (IntrospectedColumn column : table.getAllColumns()) {

					obj3[0] = mdcode;
					obj3[1] = dscode2;
					obj3[2] = ++dscode3;
					obj3[3] = column.getName();
					obj3[4] = column.getRemarks();
					try{
					obj3[5] = dbMetadataUtils.getIntrospector().getTableCreatedTime(dbMetadataUtils.getDefaultConfig(),
							str, table.getName());}
					catch(Exception e){
						
					}
					obj3[6] = column.ispk();
					obj3[7] = column.getdataType();
					resultlist3.add(obj3);
					for (Object[] objects3 : resultlist3) {

						System.out.println();
						for (int i = 0; i < 8; i++) {
							System.out.print(objects3[i] + "\t");

						}

						System.out.println(" ");
					}

					// insert.saveResults(resultlist3, sql3,conn,8);
					resultlist3.clear();

					//
					// System.out.println(column.getName() + " -pk- " +
					// column.isPk() + " - " +
					// column.getJdbcTypeName() + " ( " +
					// column.getLength() + " ) " + " -nullable- " +
					//
					//
					// column.isNullable() + " - " + " -dftvlue- " +
					//
					// column.getDefaultValue()+ " - " +
					// column.getRemarks()+ " -scale- "+
					//
					// column.getScale());
				}
			}

			conn.close();
		} catch (Exception e) {
			//mark = false;
			System.err.println( e);
			System.out.println("mark e:" + mark);
			e.printStackTrace();
		} finally {
			System.out.println("mark f:" + mark);
			return mark;
		}

	}

	public boolean getConnection(String url, String name, String password, String owner, String database) {
		boolean mark = false;
		String mdcode = null;
		String test = null;
		String pre=null;
		if (database.equals("oracle")) {
			pre="jdbc:oracle:thin:@";
			 dataSource = new SimpleDataSource(
					/*
					 * Dialect.MYSQL, "jdbc:mysql://localhost:3306/test",
					 * "root", "123"
					 */
					Dialect.ORACLE,
					// "jdbc:oracle:thin:@121.248.200.5:1521:orcl",
					// "swjsysq",
					// "swjsysq"
					"jdbc:oracle:thin:@" + url, name, password
			// Dialect.ORACLE,
			// "jdbc:oracle:thin:@10.32.1.6:1521/jssworcl",
			// "r_jssq",
			// "r_jssq"
			);
		} else {
			pre="jdbc:jtds:sqlserver://";
			dataSource = new SimpleDataSource(
					/*
					 * Dialect.MYSQL, "jdbc:mysql://localhost:3306/test",
					 * "root", "123"
					 */
					Dialect.SQLSERVER,
    		          "jdbc:jtds:sqlserver://"+url,
    		           name,
    		          password   
			// Dialect.ORACLE,
			// "jdbc:oracle:thin:@10.32.1.6:1521/jssworcl",
			// "r_jssq",
			// "r_jssq"
			);
		}
		SimpleDataSource dataSource2 = new SimpleDataSource(
				/*
				 * Dialect.MYSQL, "jdbc:mysql://localhost:3306/test", "root",
				 * "123"
				 */
//				 Dialect.ORACLE,
//				 "jdbc:oracle:thin:@121.248.200.5:1521:orcl",
//				 "SLMETADATA",
//				 "SLMETADATA"
				Dialect.ORACLE, "jdbc:oracle:thin:@10.32.25.21:1521/jssworcl", "SLMETADATA", "SLMETADATA"
				 );
		// String str="swjsysq";
		String str = owner;

		DBMetadataUtils dbMetadataUtils = null;
		DBMetadataUtils dbMetadataUtils2 = null;

		try {
			dbMetadataUtils = new DBMetadataUtils(dataSource);
			System.out.println("数据库1连接成功");
			dbMetadataUtils2 = new DBMetadataUtils(dataSource2);
			Connection conn = dbMetadataUtils2.getConnection();

			System.out.println("数据库2连接成功");

			PreparedStatement sm = null;

			int dscode1 = dbMetadataUtils.getIntrospector().getdsCode1(conn);
			int dscode2 = dbMetadataUtils.getIntrospector().getdsCode2(conn);
			int dscode3 = dbMetadataUtils.getIntrospector().getdsCode3(conn);
			sm = conn.prepareStatement(
					"select mdcode from MD_Metadata where owner=:1 and location=:2 order by builddate desc");
			sm.setString(1, owner.toUpperCase());
			sm.setString(2, pre + url);
			ResultSet rs = sm.executeQuery();
			while (rs.next()) {
				mdcode = rs.getString(1);
				break;
			}
			rs.close();
			if (mdcode == null) {
				System.out.println("无元数据信息");
				return false;
			}

			sm = conn.prepareStatement("select mdcode from MD_LibraryDSID where location=:1 and dsname=:2");
			sm.setString(1, pre + url);
			sm.setString(2, owner.toUpperCase());
			rs = sm.executeQuery();
			while (rs.next()) {
				test = rs.getString(1);
				break;
			}
			rs.close();
			if (test != null) {
				System.out.println("重复数据库");
				return false;
			}
			InsertBatch insert = new InsertBatch();
			ArrayList<Object[]> resultlist1 = new ArrayList<Object[]>();
			ArrayList<Object[]> resultlist2 = new ArrayList<Object[]>();
			ArrayList<Object[]> resultlist3 = new ArrayList<Object[]>();
			String sql1 = "INSERT INTO MD_LIBRARYDSID(mdCode,dsCode,builddate,charset,version,dsname,location,username,password)  VALUES(?,?,?,?,?,?,?,?,?)";
			String sql2 = "INSERT INTO MD_CLASSDSID(mdCode,supDscode,dsCode,dsname,beizhu,builddate)  VALUES(?,?,?,?,?,?)";
			String sql3 = "INSERT INTO MD_OBJDSID(mdCode,supDsCode,dsCode,dsName,beizhu,builddate,ispk,dataType)  VALUES(?,?,?,?,?,?,?,?)";

			Object[] obj1 = new Object[9];
			Object[] obj2 = new Object[6];
			Object[] obj3 = new Object[8];
			// obj1[0]=null;
			obj1[0] = mdcode;
			obj1[1] = dscode1 + 1;
			try{
				obj1[2] = dbMetadataUtils.getIntrospector().getDatabaseCreatedTime(dbMetadataUtils.getDefaultConfig(), str);}
				catch(Exception e){
					
				}
				try {
					obj1[3] = dbMetadataUtils.getIntrospector().getCharset(dbMetadataUtils.getDefaultConfig());
				} catch (Exception e) {
					// TODO: handle exception
				}
			
			obj1[4] = dbMetadataUtils.getDatabaseMetaData().getDatabaseProductVersion();
			// obj1[5]=dbMetadataUtils.getDefaultConfig().getSchemaPattern();
			obj1[5] = str.toUpperCase();
			obj1[6] = dbMetadataUtils.getDatabaseMetaData().getURL();
			obj1[7] = name.toUpperCase();
			obj1[8] ="";
			resultlist1.add(obj1);

			//
			// for (Object[] objects : resultlist1) {
			//
			// System.out.println();
			// for (int i = 0; i < 7; i++) {
			// System.out.print(objects[i]+"\t");
			//
			// }
			// System.out.println(" ");}
			mark = insert.saveResults(resultlist1, sql1, conn, 9);
            
			System.out.println("mark1.test " + mark);

			/*
			 * Statement statement
			 * =dbMetadataUtils.getConnection().createStatement(); String sql=
			 * "SELECT CREATED FROM USER_OBJECTS WHERE OBJECT_NAME='FX_APP_VERSION'"
			 * ; String sql1="SELECT * FROM USER_TABLES";
			 * 
			 * ResultSet rs=statement.executeQuery(sql1); int col =
			 * rs.getMetaData().getColumnCount(); while(rs.next()){ for (int i =
			 * 1; i <= col; i++) { System.out.print(rs.getString(i) + "\t"); //
			 * if ((i == 2) && (rs.getString(i).length() < 8)) { //
			 * System.out.print("\t"); // } } System.out.println("-------"); }
			 * rs.close();
			 */
			// System.out.println("数据库catlogs："+ dbMetadataUtils.getCatalogs());
			// System.out.println("数据库schema："+dbMetadataUtils.getSchemas());
			// System.out.println("数据库schematerm："+dbMetadataUtils.getDatabaseMetaData().getSchemaTerm());
			// System.out.println("数据库username："+dbMetadataUtils.getDatabaseMetaData().getUserName());
			// System.out.println("数据库URL: " +
			// dbMetadataUtils.getDatabaseMetaData().getURL());
			// System.out.println("是否允许只读:" +
			// dbMetadataUtils.getDatabaseMetaData().isReadOnly());
			// System.out.println("数据库的产品名称:" +
			// dbMetadataUtils.getDatabaseMetaData().getDatabaseProductName());
			// System.out.println("数据库的版本:" +
			// dbMetadataUtils.getDatabaseMetaData().getDatabaseProductVersion());
			// System.out.println("数据库的schemapattern:" +
			// dbMetadataUtils.getDefaultConfig().getSchemaPattern());
			// System.out.println("数据库建立时间:" +
			// dbMetadataUtils.getIntrospector().getDatabaseCreatedTime(dbMetadataUtils.getDefaultConfig()));
			// System.out.println("数据库的字符集:" +
			// dbMetadataUtils.getIntrospector().getCharset(dbMetadataUtils.getDefaultConfig()));

			/*
			 * System.out.println("数据库中使用的表类型"); ResultSet rs =
			 * dbMetadataUtils.getDatabaseMetaData().getClientInfoProperties();
			 * while (rs.next()) { System.out.println(rs.getString(1)); }
			 * rs.close();
			 */

			List<IntrospectedTable> list = dbMetadataUtils.introspectTables(dbMetadataUtils.getDefaultConfig(), str);
			//
			// for (IntrospectedTable base : list) {
			// System.out.println(base.getRemarks() + ":"); }
			// System.out.println("---------------------------");
			// for (IntrospectedTable base : list) {
			// System.out.println(base.getCatalog() + ":"); }
			// System.out.println("---------------------------");
			// for (IntrospectedTable base : list) {
			// System.out.println(base.getName() + ":"); }
			// System.out.println("---------------------------");
			//

			for (IntrospectedTable table : list) {

				if (!mark)
					break;
				obj2[0] = mdcode;
				obj2[1] = dscode1 + 1;
				obj2[2] = ++dscode2;
				obj2[3] = table.getName();
				obj2[4] = table.getRemarks();
				try{
					obj2[5] = dbMetadataUtils.getIntrospector().getTableCreatedTime(dbMetadataUtils.getDefaultConfig(),
							str, table.getName());}
					catch(Exception e){
						
					}
			

				resultlist2.add(obj2);
				// for (Object[] objects2 : resultlist2) {
				//
				// System.out.println();
				//
				// for (int i = 0; i < 6; i++) {
				// System.out.print(objects2[i]+"\t");
				//
				// }
				// System.out.println(" ");}
				mark = insert.saveResults(resultlist2, sql2, conn, 6);
				System.out.println("mark2 " + mark);

				resultlist2.clear();
				// System.out.println(table.getName() + ":");
				// System.out.println(table.getRemarks() + ":");
				// System.out.println("表建立时间:" +
				// dbMetadataUtils.getIntrospector().getTableCreatedTime(dbMetadataUtils.getDefaultConfig(),table.getName()));
				// System.out.println("表DML时间:" +
				// dbMetadataUtils.getIntrospector().getTableDMLTime(dbMetadataUtils.getDefaultConfig(),table.getName()));
				// System.out.println("表Rows:" +
				// dbMetadataUtils.getIntrospector().getRowNum(dbMetadataUtils.getDefaultConfig(),table.getName()));
				// System.out.println("表AnalyzedTime:" +
				// dbMetadataUtils.getIntrospector().getAnalyzedTime(dbMetadataUtils.getDefaultConfig(),table.getName()));
				for (IntrospectedColumn column : table.getAllColumns()) {

					if (!mark)
						break;

					obj3[0] = mdcode;
					obj3[1] = dscode2;
					obj3[2] = ++dscode3;
					obj3[3] = column.getName();
					obj3[4] = column.getRemarks();
					try{
						obj3[5] = dbMetadataUtils.getIntrospector().getTableCreatedTime(dbMetadataUtils.getDefaultConfig(), str,
								table.getName());}
						catch (Exception e){
							
						}
					obj3[6] = column.ispk();
					obj3[7] = column.getdataType();
					resultlist3.add(obj3);
					// for (Object[] objects3 : resultlist3) {
					//
					// System.out.println();
					// for (int i = 0; i < 8; i++) {
					// System.out.print(objects3[i]+"\t");
					//
					// }
					//
					// System.out.println(" ");}

		 			mark = insert.saveResults(resultlist3, sql3, conn, 8);
					System.out.println("mark3 " + mark);
					resultlist3.clear();

					//
					// System.out.println(column.getName() + " -pk- " +
					// column.isPk() + " - " +
					// column.getJdbcTypeName() + " ( " +
					// column.getLength() + " ) " + " -nullable- " +
					//
					//
					// column.isNullable() + " - " + " -dftvlue- " +
					//
					// column.getDefaultValue()+ " - " +
					// column.getRemarks()+ " -scale- "+
					//
					// column.getScale());
				}
			}

			conn.close();
		} catch (Exception e) {
			mark = false;
			System.out.println(mark);
		} finally {
			System.out.println("mark.get f:" + mark);
			return mark;
		}

	}
}
